﻿using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ESRI.ArcGIS.DataSourcesFile;
using ESRI.ArcGIS.Display;
using ESRI.ArcGIS.Editor;
using ESRI.ArcGIS.EditorExt;
using ESRI.ArcGIS.esriSystem;
using ESRI.ArcGIS.Geodatabase;
using ESRI.ArcGIS.Geometry;
using ESRI.ArcGIS.GlobeCore;
using ESRI.ArcGIS.NetworkAnalysis;
using ESRI.ArcGIS.Output;
using ESRI.ArcGIS.SystemUI;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using System.Data;
using DIWAGIS.Modules;
using Microsoft.Office;
using DIWAGIS.Class;
using System.Reflection;

namespace DIWAGIS.ApplicationLayer.ThongKeTaiSan
{
    public partial class frmThongKeDongHoKhachHang : Form
    {
        string sTableName = generalVariable.database + "." + generalVariable.schema + "." + ClassVariables.wDongHoKhachHang;
        int sNResuilt = 0;
        Boolean kt = false;
        public frmThongKeDongHoKhachHang()
        {
            InitializeComponent();
        }
        
        Boolean isLoadComBox = false;
        
        private void buttonTimKiem_Click(object sender, EventArgs e)
        {
            IFeatureWorkspace pFWS = DIWAGIS.Modules.generalVariable.ICurrentWorkspace as IFeatureWorkspace;

            string strWhere = "";

            if (txtMaDMA.Text != string.Empty)
            {
                if (strWhere.ToString() != string.Empty)
                {
                    //strWhere = strWhere & " AND  (CoDongHo= " & Int16.Parse(cbxCoDongHo.SelectedValue) & ")"
                    strWhere = strWhere + " AND MaDMA ='" + (txtMaDMA.Text) + "'";
                }
                else
                {
                    strWhere = "MaDMA ='" + (txtMaDMA.Text) + "'";
                }
            }
            if (comboBoxHieuDH.Text != string.Empty)
            {
                if (strWhere.ToString() != string.Empty)
                {
                    //strWhere = strWhere & " AND  (CoDongHo= " & Int16.Parse(cbxCoDongHo.SelectedValue) & ")"
                    strWhere = strWhere + " AND HieuDH ='" + (comboBoxHieuDH.SelectedValue) + "'";
                }
                else
                {
                    strWhere = "HieuDH ='" + (comboBoxHieuDH.SelectedValue) + "'";
                }
            }

            if (comboBoxCoDongHo.Text != string.Empty)
            {
                if (strWhere.ToString() != string.Empty)
                {
                    //strWhere = strWhere & " AND  (CoDongHo= " & Int16.Parse(cbxCoDongHo.SelectedValue) & ")"
                    strWhere = strWhere + " AND CoDongHo = " + (comboBoxCoDongHo.SelectedValue);
                }
                else
                {
                    strWhere = "CoDongHo = " + (comboBoxCoDongHo.SelectedValue);
                }
            }

            if (comboBoxQuanHuyen.Text != string.Empty)
            {
                if (strWhere.ToString() != string.Empty)
                {
                    //strWhere = strWhere & " AND  (CoDongHo= " & Int16.Parse(cbxCoDongHo.SelectedValue) & ")"
                    strWhere = strWhere + " AND Huyen =N'" + (comboBoxQuanHuyen.SelectedValue) + "'";
                }
                else
                {
                    strWhere = "Huyen =N'" + (comboBoxQuanHuyen.SelectedValue) + "'";
                }
            }

            if (comboBoxTuyenDuong.Text != string.Empty)
            {
                if (strWhere.ToString() != string.Empty)
                {
                    //strWhere = strWhere & " AND  (CoDongHo= " & Int16.Parse(cbxCoDongHo.SelectedValue) & ")"
                    strWhere = strWhere + " AND TenDuong =N'" + (comboBoxTuyenDuong.SelectedValue) + "'";
                }
                else
                {
                    strWhere = "TenDuong =N'" + (comboBoxTuyenDuong.SelectedValue) + "'";
                }
            }

            if (comboBoxPhuongXa.Text != string.Empty)
            {
                if (strWhere.ToString() != string.Empty)
                {
                    //strWhere = strWhere & " AND  (CoDongHo= " & Int16.Parse(cbxCoDongHo.SelectedValue) & ")"
                    strWhere = strWhere + " AND Phuong =N'" + (comboBoxPhuongXa.SelectedValue) + "'";
                }
                else
                {
                    strWhere = "Phuong =N'" + (comboBoxPhuongXa.SelectedValue) + "'";
                }
            }

            // ITable pTable = pFWS.OpenTable("gis_water.DBO.fcDongHoKhachHang") as ITable;

            IQueryDef queryDef = pFWS.CreateQueryDef();

            queryDef.Tables = sTableName;

            queryDef.SubFields = "HieuDH,CoDongHo,Phuong,Huyen,MaDMA,TenDuong";

            // Assign a where clause to filter the results.
            queryDef.WhereClause = strWhere;
            DIWAGIS.Class.WorkingWithTable pC = new DIWAGIS.Class.WorkingWithTable();
            ITable pTable = pC.CreateQueryTable(DIWAGIS.Modules.generalVariable.ICurrentWorkspace, queryDef, sTableName);
            
            sNResuilt = pTable.RowCount(queryDef as IQueryFilter);

            dataGridView1.Rows.Add(comboBoxQuanHuyen.Text, comboBoxPhuongXa.Text, comboBoxTuyenDuong.Text, comboBoxHieuDH.Text, comboBoxCoDongHo.Text, sNResuilt.ToString());
           

            //if (sNResuilt > 0)
            //{
            //    lb_message.Text = "Có " + (sNResuilt.ToString()) + " đối tượng thỏa mãn tiêu chí thống kê ";
            //}
           
            
            //dataGridView1.Columns.Clear();            
            //TableWrapper m_tableWrapper = new TableWrapper(pTable);
            //m_tableWrapper.UseCVDomains = true;           
            //bindingSourceDongHoKhachHang.DataSource = m_tableWrapper;           
            //dataGridView1.DataSource = bindingSourceDongHoKhachHang;            
        }

        private void frmThongKeDongHoKhachHang_Load(object sender, EventArgs e)
        {
            buttonDung.Visible = false;
            progressBarTienTrinh.Visible = false;
            DIWAGIS.Module.WorkingWithDomain cbx = new DIWAGIS.Module.WorkingWithDomain();
            cbx.loadDataDomainToCombobox(comboBoxHieuDH, DIWAGIS.Modules.generalVariable.ICurrentWorkspace, "HIEU_DH");
            cbx.loadDataDomainToCombobox(comboBoxCoDongHo, DIWAGIS.Modules.generalVariable.ICurrentWorkspace, "DMCoDongHoCon");
            SqlCommand sqlcommand = new SqlCommand();
            sqlcommand.CommandText = "SELECT TENHUYEN,TENHUYEN FROM HUYEN ";
            cbx.loadDataToCombobox(comboBoxQuanHuyen, sqlcommand);

            sqlcommand.CommandText = "SELECT TENCONDUONG,TENCONDUONG FROM " + generalVariable.database + "." + generalVariable.schema + "." + " CONDUONG ";
            cbx.loadDataToCombobox(comboBoxTuyenDuong, sqlcommand);

            isLoadComBox = true;
        }

        private void buttonHuyBo_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void comboBoxQuanHuyen_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (isLoadComBox == true)
            {
                string strWhere = "";
                if (!string.IsNullOrEmpty(comboBoxQuanHuyen.Text))
                {
                    strWhere = comboBoxQuanHuyen.SelectedValue.ToString();
                    SqlCommand sqlcommand = new SqlCommand();
                    DIWAGIS.Module.WorkingWithDomain cbx = new DIWAGIS.Module.WorkingWithDomain();
                    //sqlcommand = new System.Data.SqlClient.SqlCommand();
                    sqlcommand.CommandText = "SELECT TenHanhChinh,TenHanhChinh FROM " + generalVariable.database + "." + generalVariable.schema + "." + "HANHCHINH" + " WHERE TenHuyen = @TenHuyen";
                    sqlcommand.Parameters.AddWithValue("@TenHuyen", strWhere);
                    cbx.loadDataToCombobox(comboBoxPhuongXa, sqlcommand);
                    //loadDataToCombobox(cbxMaPhuong, sqlcommand);

                }
            }
        }

        private void buttonXuatBaoCao_Click(object sender, EventArgs e)
        {
        
        }
       
        private void ExportDataGridViewToExcel()
        {

            try
            {
                progressBarTienTrinh.Visible = true;
                progressBarTienTrinh.Minimum = 0;
                progressBarTienTrinh.Maximum = sNResuilt;
                Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();

                // creating new WorkBook within Excel application
                Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);

                // creating new Excelsheet in workbook
                Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

                // see the excel sheet behind the program
                app.Visible = false;

                // get the reference of first sheet. By default its name is Sheet1.
                // store its reference to worksheet
                worksheet = workbook.Sheets["Sheet1"];
                worksheet = workbook.ActiveSheet;

                // changing the name of active sheet
                worksheet.Name = "Kết quả báo cáo";
                object misValue = System.Reflection.Missing.Value;
                workbook = app.Workbooks.Add(misValue);
                worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(1);
                // storing header part in Excel

                for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
                {
                    worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
                }
                int dem = 0;
                // storing Each row and column value to excel sheet
                for (int i = 0; i < dataGridView1.Rows.Count ; i++)
                {
                    progressBarTienTrinh.Value = i + 1;
                    for (int j = 0; j < dataGridView1.Columns.Count ; j++)
                    {
                        if (dataGridView1.Rows[i].Cells[j].Value != null)
                            worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                        else
                            worksheet.Cells[i + 2, j + 1] = "";
                        dem = j;
                    }
                    Application.DoEvents();
                    
                    if (kt == true)
                    {
                        break;
                    }
                }



                //ve bieu do

             
                //tam bo

                //Excel.Range chartRange;

                //Excel.ChartObjects xlCharts = (Excel.ChartObjects)worksheet.ChartObjects(Type.Missing);
                //Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
                //Excel.Chart chartPage = myChart.Chart;

                ////Chart chart = sheet.Charts.Add(ExcelChartType.Column3DClustered);
                //string stt=Convert.ToString(dem+1);
                ////MessageBox.Show(dem.ToString());
                //chartRange = worksheet.get_Range("A2", "F" + stt);
                //chartPage.SetSourceData(chartRange, Excel.XlRowCol.xlColumns);
                //chartPage.ChartType = Excel.XlChartType.xl3DColumnClustered;

                // tam bo
               
                //export chart as picture file
                //chartPage.Export(@"D:\Chart_Export.bmp", "BMP", misValue);

                //workbook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                //workbook.Close(true, misValue, misValue);


                worksheet.Columns.AutoFit();
                SaveFileDialog f = new SaveFileDialog();

                if (f.ShowDialog() == DialogResult.OK)
                {
                    if (System.IO.File.Exists(f.FileName))
                    {
                        System.IO.File.Delete(f.FileName);
                    }

                    workbook.SaveAs(f.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                    //workbook.Close(true, misValue, misValue);
                    //app.Quit();

                    releaseObject(worksheet);
                    releaseObject(workbook);
                    releaseObject(app);

                    //frm_message.showMesage("Chương trình đã xuất file thành công", 1);
                }
              
            }
            catch
            {
                MessageBox.Show("Thông báo! Lỗi trong quá trình xuất báo cáo ", generalVariable.nameProgram, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }


        }
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
        private void buttonDung_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Bạn có chắc muốn dừng lại không ?", "Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) == DialogResult.Yes)
            {
                kt = true;
            }
        }

        private void buttonXuatBaoCao_Click_1(object sender, EventArgs e)
        {
            // creating Excel Application
            buttonXuatBaoCao.Visible = false;
            buttonDung.Visible = true;

            ExportDataGridViewToExcel();
            buttonDung.Visible = false;
            buttonXuatBaoCao.Visible = true;
        }
    }
}
